package com.example.filetransfer.util;

import com.example.filetransfer.model.Message;
import com.example.filetransfer.model.Message.MessageType;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class DatabaseUtil {
    private static final String DB_URL = "jdbc:sqlite:messages.db";
    private static final String CREATE_TABLE_SQL = 
        "CREATE TABLE IF NOT EXISTS messages (" +
        "id INTEGER PRIMARY KEY AUTOINCREMENT," +
        "content TEXT NOT NULL," +
        "sender TEXT NOT NULL," +
        "timestamp DATETIME NOT NULL," +
        "type TEXT NOT NULL" +
        ")";

    static {
        try {
            Class.forName("org.sqlite.JDBC");
            initializeDatabase();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    private static void initializeDatabase() {
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.execute(CREATE_TABLE_SQL);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(DB_URL);
    }

    public static void saveMessage(Message message) {
        String sql = "INSERT INTO messages (content, sender, timestamp, type) VALUES (?, ?, ?, ?)";
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, message.getContent());
            pstmt.setString(2, message.getSender());
            pstmt.setString(3, message.getTimestamp().toString());
            pstmt.setString(4, message.getType().name());
            
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static List<Message> getRecentMessages(int limit) {
        List<Message> messages = new ArrayList<>();
        String sql = "SELECT * FROM messages ORDER BY timestamp DESC LIMIT ?";
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, limit);
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Message message = new Message();
                message.setId(rs.getLong("id"));
                message.setContent(rs.getString("content"));
                message.setSender(rs.getString("sender"));
                message.setTimestamp(LocalDateTime.parse(rs.getString("timestamp")));
                message.setType(MessageType.valueOf(rs.getString("type")));
                messages.add(message);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return messages;
    }

    public static List<Message> getMessagesBySender(String sender) {
        List<Message> messages = new ArrayList<>();
        String sql = "SELECT * FROM messages WHERE sender = ? ORDER BY timestamp DESC";
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, sender);
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Message message = new Message();
                message.setId(rs.getLong("id"));
                message.setContent(rs.getString("content"));
                message.setSender(rs.getString("sender"));
                message.setTimestamp(LocalDateTime.parse(rs.getString("timestamp")));
                message.setType(MessageType.valueOf(rs.getString("type")));
                messages.add(message);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return messages;
    }

    public static void deleteOldMessages(int daysToKeep) {
        String sql = "DELETE FROM messages WHERE timestamp < datetime('now', ?)";
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, "-" + daysToKeep + " days");
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
} 